package tk.cful.core;

import java.sql.*;
import java.util.*;

/**
 * User: Root
 * Date: 08.11.13
 * Time: 19:45
 */
public class DBManager {

    private static DBManager manager = null;

    private Connection con;
    private PreparedStatement stmt;
    private ResultSet rs;

    private DBManager() {
    }

    public static DBManager getInstance() {
        if (manager == null) {
            manager = new DBManager();
            manager.loadDriver();
            manager.makeConnection();
        }
        return manager;
    }

    public UserEntity findUserByID(int userID) {
        try {
            stmt = con.prepareStatement(findUserByIDQuery());
            stmt.setInt(1, userID);
            rs = stmt.executeQuery();
            if (rs.next())
                return new UserEntity(
                        rs.getInt("user_id"),
                        rs.getString("user_name"),
                        rs.getString("user_password")
                );
            return null;
        } catch (SQLException e) {
            return null;
        }
    }

    public UserEntity findUserByName(String userName) {
        try {
            stmt = con.prepareStatement(findUserByNameQuery());
            stmt.setString(1, userName);
            rs = stmt.executeQuery();
            if (rs.next())
                return new UserEntity(
                        rs.getInt("user_id"),
                        rs.getString("user_name"),
                        rs.getString("user_password")
                );
            return null;
        } catch (SQLException e) {
            return null;
        }
    }

    public FileEntity findFileByID(int fileID) {
        try {
            stmt = con.prepareStatement(findFileByIDQuery());
            stmt.setInt(1, fileID);
            rs = stmt.executeQuery();
            if (rs.next())
                return new FileEntity(
                        rs.getInt("file_id"),
                        rs.getString("file_name"),
                        rs.getString("path"),
                        rs.getInt("user_id"),
                        rs.getString("hash_name")
                );
            return null;
        } catch (SQLException e) {
            return null;
        }
    }

    public String getNameByHashName(String hashName) {
        try {
            stmt = con.prepareStatement(getNameByHashName());
            stmt.setString(1, hashName);
            rs = stmt.executeQuery();
            String result = "";
            if (rs.next())
                result = rs.getString(1);
            return result;
        } catch (SQLException e) {
            return "";
        }

    }

    public int generateFileID() {
        try {
            stmt = con.prepareStatement(generateFileIDQuery());
            rs = stmt.executeQuery();
            if (rs.next())
                return (rs.getInt("max_id") + 1);
            return 0;
        } catch (SQLException e) {
            return 0;
        }
    }

    public int generateUserID() {
        try {
            stmt = con.prepareStatement(generateUserIDQuery());
            rs = stmt.executeQuery();
            if (rs.next())
                return (rs.getInt("max_id") + 1);
            return 0;
        } catch (SQLException e) {
            return 0;
        }
    }

    public List<FileEntity> findFilesByUserID(int userID) {
        try {
            List<FileEntity> result = new ArrayList<FileEntity>();
            stmt = con.prepareStatement(findFilesByUserIDQuery());
            stmt.setInt(1, userID);
            rs = stmt.executeQuery();
            while (rs.next())
                result.add(new FileEntity(
                        rs.getInt("file_id"),
                        rs.getString("file_name"),
                        rs.getString("path"),
                        rs.getInt("user_id"),
                        rs.getString("hash_name")
                ));
            return result;
        } catch (SQLException e) {
            return null;
        }
    }

    public boolean addUser(int userID, String userName, String password) {
        try {
            stmt = con.prepareStatement(addUserQuery());
            stmt.setInt(1, userID);
            stmt.setString(2, userName);
            stmt.setString(3, password);
            if (stmt.executeUpdate() == 0)
                return false;
            return true;
        } catch (SQLException e) {
            return false;
        }
    }

    public boolean addFile(int fileID, String fileName, String path, int ownerID, String hash) {
        try {
            stmt = con.prepareStatement(addFileQuery());
            stmt.setInt(1, fileID);
            stmt.setString(2, fileName);
            stmt.setString(3, path);
            stmt.setInt(4, ownerID);
            stmt.setString(5, hash);
            if (stmt.executeUpdate() == 0)
                return false;
            return true;
        } catch (SQLException e) {
            return false;
        }
    }

    public String getHashName(String name) {
        try {
            stmt = con.prepareStatement(getHashName());
            stmt.setString(1, name);
            rs = stmt.executeQuery();
            String result = "";
            if (rs.next())
                result = rs.getString(1);
            return result;
        } catch (SQLException e) {
            return "";
        }
    }

    private void loadDriver() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (java.lang.ClassNotFoundException e) {
            System.err.print("ClassNotFoundException: ");
            System.err.println(e.getMessage());
        }
    }

    private void makeConnection() {
        Locale.setDefault(Locale.ENGLISH);
        String host = "127.0.0.1";
        String dbName = "xe";
        int port = 1521;
        String oracleURL = "jdbc:oracle:thin:@" + host +
                ":" + port + ":" + dbName;
        try {
            con = DriverManager.getConnection(oracleURL, "TEST", "TEST");
        } catch (SQLException ex) {
            System.err.println("database connection: " + ex.getMessage());
        }
    }

    private String findUserByIDQuery() {
        return "select * from cful_users where user_id = ?";
    }

    private String findUserByNameQuery() {
        return "select * from cful_users where user_name = ?";
    }


    private String findFileByIDQuery() {
        return "select * from cful_files where file_id = ?";
    }

    private String findFilesByUserIDQuery() {
        return "select * from cful_files where user_id = ?";
    }

    private String addUserQuery() {
        return "insert into cful_users values(?,?,?)";
    }

    private String addFileQuery() {
        return "insert into cful_files values(?,?,?,?,?)";
    }

    private String getHashName() {
        return "select hash_name from cful_files where file_name = ?";
    }

    private String getNameByHashName() {
        return "select file_name from cful_files where hash_name = ?";
    }

    private String generateFileIDQuery() {
        return "select max(file_id) max_id from cful_files";
    }

    private String generateUserIDQuery() {
        return "select max(user_id) max_id from cful_users";
    }


}
